<?php defined( '_VALID_MOS' ) or die( 'Restricted access' );?>
<P align="left">
<IMG src="components/com_jcs/subscription.gif" height="16" width="13" align="absmiddle">
<a href="index2.php?option=com_jcs">Subscription Plans</a>
&nbsp;&nbsp;&nbsp;
<IMG src="/includes/js/ThemeOffice/add_section.png" height="16" width="16" align="absmiddle">
<a href="index2.php?option=com_categories&section=com_jcs_plans">Subscription Categories</a>
&nbsp;&nbsp;&nbsp;
<IMG src="components/com_jcs/usubscription.gif" height="16" width="16" align="absmiddle">
<a href="index2.php?option=com_jcs&task=usub">User Subscriptions</a>
&nbsp;&nbsp;&nbsp;
<IMG src="components/com_jcs/config.png" height="16" width="16" align="absmiddle">
<a href="index2.php?option=com_jcs&task=config">Configuration</a>
&nbsp;&nbsp;&nbsp;
<IMG src="components/com_jcs/reports16.png" height="16" width="16" align="absmiddle">
<a href="index2.php?option=com_jcs&task=report">Reports</a>
&nbsp;&nbsp;&nbsp;
<?php 

$c_from = mosGetParam( $_REQUEST, 'from');
$c_to   = mosGetParam( $_REQUEST, 'to');
$c_year = mosGetParam( $_REQUEST, 'show_year');

if($GLOBALS['juser'])
{
    ?>
<IMG src="<?php echo $mosConfig_live_site; ?>/includes/js/ThemeOffice/users.png" height="16" width="16" align="absmiddle">
<a href="index2.php?option=com_juser">JUser Management</a>
&nbsp;&nbsp;&nbsp;
    <?php
}
 ?>
</P>
<div align="left">
<div align="left" class="small">Subscription Progress For for last 30 days (1 Month).</div>
<?php
global $database, $mosConfig_live_site, $mosConfig_cachepath;
$sql = "
    SELECT sum(u.price) as price, count(s.id) as total, 'asd' as asd
      FROM #__jcs_user_subscr AS u
 LEFT JOIN #__jcs_plans AS s ON s.id = u.subscription_id
     WHERE u.created > '$c_from' AND u.created < '$c_to' AND u.published = 1
GROUP BY asd";
$database->setQuery($sql);
$rows = $database->loadObjectList();
//echo $sql;
//echo $database->stderr();
$totals = $rows[0];
//print_r($totals);
$sql = "
    SELECT sum(u.price) as price, count(s.id) as total, 
    DATE_FORMAT(u.created, '%b') as mn, 
    DATE_FORMAT(u.created, '%d') as dy, 
    DATE_FORMAT(u.created, '%c') as mnn
      FROM #__jcs_user_subscr AS u
 LEFT JOIN #__jcs_plans AS s ON s.id = u.subscription_id
     WHERE u.created > '$c_from' AND u.created < '$c_to' AND u.published = 1
 GROUP BY dy
 ORDER BY u.created ASC";

$database->setQuery($sql);
$rows = $database->loadObjectList();
unset($l, $d);
foreach($rows AS $row)
{
    $l[]  = $row->dy."\n".$row->mn;
    $d2[] = $row->price;
    $d[]  = $row->total;
    $d3[] = ($row->price / ($totals->price / 100)) + ($row->total / ($totals->total / 100));
}

$l = urlencode(serialize($l));
$d = urlencode(serialize($d));
$d2 = urlencode(serialize($d2));
$d3 = urlencode(serialize($d3));
echo '<img src="'.$mosConfig_live_site.'/administrator/components/com_jcs/jpgraph/bar.plot.php?c=2&l='.$l.'&d='.$d.'&d2='.$d2.'&d3='.$d3.'&t=Subscription Progress for Last 30 days&w=700&h=230" alt="Test">';
$sql = "
    SELECT sum(u.price) as price, count(s.id) as total, s.name, s.price AS sprice,
    DATE_FORMAT(u.created, '%b') as mn, DATE_FORMAT(u.created, '%d') as dy,
DATE_FORMAT(u.created, '%d, %b %Y') AS dat
      FROM #__jcs_user_subscr AS u
 LEFT JOIN #__jcs_plans AS s ON s.id = u.subscription_id
     WHERE u.created > '$c_from' AND u.created < '$c_to' AND u.published = 1
 GROUP BY dy, s.name
 ORDER BY u.created DESC";

$database->setQuery($sql);
$rows = $database->loadObjectList();
ob_start()
?>
<TABLE class="adminform" style="width:700px" width="700px">
  <TR>
    <Th width="25">#</Th>
    <Th width="90">Data</Th>
    <Th>Subscription</Th>
    <Th width="70">Price</Th>
    <Th width="65">Amount</Th>
    <Th width="70">total</Th>
  </TR>
<?php 
foreach($rows AS $row)
{
$k = 1 - $k;
?>
  <TR class="row<?php echo $k?>">
    <TD><?php echo ++$i;?></TD>
    <TD nowrap="nowrap"><?php echo $row->dat?></TD>
    <TD><?php echo $row->name?></TD>
    <TD align="right">$<?php echo $row->sprice?></TD>
    <TD><?php echo $row->total?></TD>
    <TD align="right">$<?php echo number_format($row->price, 2, '.','')?></TD>
  </TR>
<?php
  $tot[] = $row->price;
}
?>
  <TR>
    <TD colspan="4" align="right"></TD>
    <TD><B>Total:</B></TD>
    <TD align="right"><B>$<?php echo number_format(array_sum($tot), 2, '.','')?></B></TD>
  </TR>

</table>
<?php
$table = ob_get_contents ( );
ob_end_clean();
$file = $mosConfig_cachepath.'/last_month_subscriptions.xls';
file_put_contents($file, $table);
?>
<div align="left">
Download this table as Microsoft &reg; Office Excel File <img alt="" src="components/com_jcs/ico_xls.gif">
<a href="<?php echo str_replace($mosConfig_cachepath, $mosConfig_live_site.'/cache', $file);?>">last_month_subscriptions.xls</a>
</div><BR>

<?php
echo $table;
?>
<BR>
<BR>
<?php if($c_year)
{
?>
    <div align="left" class="small">Subscription Progress For for last Year.</div>
    <?php
    $sql = "
        SELECT sum(u.price) as price, count(s.id) as total, 'asd' as asd
          FROM #__jcs_user_subscr AS u
     LEFT JOIN #__jcs_plans AS s ON s.id = u.subscription_id
         WHERE u.created > NOW() - INTERVAL 1 YEAR AND u.published = 1
    GROUP BY asd
    ORDER BY u.created ASC";
    $database->setQuery($sql);
    $rows = $database->loadObjectList();
    $totals = $rows[0];
    
    $sql = "
        SELECT sum(u.price) AS price, count(s.id) as total, 
        DATE_FORMAT(u.created, '%b') as mn, DATE_FORMAT(u.created, '%Y') AS year
          FROM #__jcs_user_subscr AS u
     LEFT JOIN #__jcs_plans AS s ON s.id = u.subscription_id
         WHERE u.created > NOW() - INTERVAL 1 YEAR AND u.published = 1
     GROUP BY mn
     ORDER BY  u.created ASC";
    
    $database->setQuery($sql);
    $rows = $database->loadObjectList();
    unset($l, $d, $d2, $d3);
    foreach($rows AS $row)
    {
        $l[]  = $row->mn."\n".$row->year;
        $d2[] = $row->price;
        $d[]  = $row->total;
        $d3[] = ($row->price / ($totals->price / 100)) + ($row->total / ($totals->total / 100));
    }
    $l = urlencode(serialize($l));
    $d = urlencode(serialize($d));
    $d2 = urlencode(serialize($d2));
    $d3 = urlencode(serialize($d3));
    echo '<img src="'.$mosConfig_live_site.'/administrator/components/com_jcs/jpgraph/bar.plot.php?c=1&l='.$l.'&d='.$d.'&d2='.$d2.'&d3='.$d3.'&t=Subscription Progress for Last Year&w=700&h=230" alt="Test">';
}
?>
<BR>
<BR>
<div align="left" class="small">This plot shows the relation beetween subscriptions for last 30 days.</div>
<?php
$sql = "
   SELECT s.id, s.name, count(u.id) as total 
     FROM #__jcs_user_subscr AS u 
LEFT JOIN #__jcs_plans AS s ON s.id = u.subscription_id
     WHERE u.created > '$c_from' AND u.created < '$c_to' AND u.published = 1
 GROUP BY u.subscription_id
 ORDER BY u.subscription_id DESC";

$database->setQuery($sql);
$rows = $database->loadObjectList();
unset($l, $d, $d2);
foreach($rows AS $row)
{
    $l[] = strlen($row->name) > 45 ? substr($row->name, 0, 40).'...' : $row->name;
    $d[] = $row->total;
}
$sql = "
    SELECT s.name, sum(u.price) as ptotal
      FROM #__jcs_user_subscr AS u
 LEFT JOIN #__jcs_plans AS s ON s.id = u.subscription_id
     WHERE u.created > '$c_from' AND u.created < '$c_to' AND u.published = 1
 GROUP BY u.subscription_id
 ORDER BY u.subscription_id DESC";

$database->setQuery($sql);
$rows2 = $database->loadObjectList();
foreach($rows2 AS $row)
{
    $d2[] = $row->ptotal;
}
$_l = urlencode(serialize($l));
$_d = urlencode(serialize($d));
$_d2 = urlencode(serialize($d2));
echo '<img src="'.$mosConfig_live_site.'/administrator/components/com_jcs/jpgraph/dcycle.plot.php?c=0&l='.$_l.'&d='.$_d.'&d2='.$_d2.'&t2=Income Relation ($'.array_sum($d2).')&t1=Count Relation ('.array_sum($d).')&t=Last Month Subscription&w=750&h=250" alt="Plot">';

ob_start()
?>
<TABLE class="adminform" style="width:700px" width="700px">
  <TR>
    <Th width="25">#</Th>
    <Th>Subscription</Th>
    <Th width="80">Amount (total)</Th>
    <Th width="65">Amount (%)</Th>
    <Th width="70">Price Total</Th>
    <Th width="70">Price (%)</Th>
  </TR>
<?php 
foreach($rows AS $key => $row)
{
$k = 1 - $k;
?>
  <TR class="row<?php echo $k?>">
    <TD><?php echo $row->id;?></TD>
    <TD><?php echo $row->name?></TD>
    <TD><?php echo $row->total?></TD>
    <TD><?php echo number_format(($row->total / (array_sum($d) / 100)), 2, '.','');  ?>%</TD>
    <TD>$<?php echo $rows2[$key]->ptotal?></TD>
    <TD><?php echo number_format(($rows2[$key]->ptotal / (array_sum($d2) / 100)), 2, '.','');  ?>%</TD>
  </TR>
<?php
  $tot2[] = $rows2[$key]->ptotal;
  $tot3[] = $row->total;
}
?>
  <TR>
    <TD>&nbsp; </TD>
    <TD style="text-align:right"><B>Total:</B></TD>
    <TD><B><?php echo array_sum($tot3)?> Units</B></TD>
    <TD>&nbsp;</TD>
    <TD><B>$<?php echo number_format(array_sum($tot2), 2, '.','')?></B></TD>
    <TD>&nbsp;</TD>
  </TR>
</table>
<?php
$table = ob_get_contents ( );
ob_end_clean();
$file = $mosConfig_cachepath.'/last_month_subscriptions_relation.xls';
file_put_contents($file, $table);
?>
<div align="left">
Download this table as Microsoft &reg; Office Excel File <img alt="" src="components/com_jcs/ico_xls.gif">
<a href="<?php echo str_replace($mosConfig_cachepath, $mosConfig_live_site.'/cache', $file);?>">last_month_subscriptions_relation.xls</a>
</div><BR>

<?php
echo $table;
?>
<BR>
<BR>
<?php if($c_year)
{
?>
    <div align="left" class="small">This plot shows the relation beetween subscriptions for last Year.</div>
    <?php
    $sql = "
       SELECT s.id, s.name, count(u.id) as total 
         FROM #__jcs_user_subscr AS u 
    LEFT JOIN #__jcs_plans AS s ON s.id = u.subscription_id
        WHERE u.created > NOW() - INTERVAL 1 YEAR AND u.published = 1
     GROUP BY u.subscription_id
     ORDER BY u.subscription_id DESC";
    
    $database->setQuery($sql);
    $rows = $database->loadObjectList();
    
    $sql = "
        SELECT s.name, sum(u.price) as ptotal
          FROM #__jcs_user_subscr AS u
     LEFT JOIN #__jcs_plans AS s ON s.id = u.subscription_id
         WHERE u.created > NOW() - INTERVAL 1 YEAR AND u.published = 1
     GROUP BY u.subscription_id
     ORDER BY u.subscription_id DESC";
    
    $database->setQuery($sql);
    $rows2 = $database->loadObjectList();
    
    unset($l, $d, $d2);
    
    foreach($rows AS $key => $row)
    {
        $l[] = strlen($row->name) > 45 ? substr($row->name, 0, 40).'...' : $row->name;
        $d[] = $row->total;
        $d2[] = $rows2[$key]->ptotal;
    }
    $_l = urlencode(serialize($l));
    $_d = urlencode(serialize($d));
    $_d2 = urlencode(serialize($d2));
    echo '<img src="'.$mosConfig_live_site.'/administrator/components/com_jcs/jpgraph/dcycle.plot.php?c=2&l='.$_l.'&d='.$_d.'&d2='.$_d2.'&t2=Income Relation ($'.array_sum($d2).')&t1=Count Relation ('.array_sum($d).')&t=Last Year Subscription&w=750&h=250" alt="Plot">';
}?>

</div>
